-- Create the Replication publication
CREATE PUBLICATION supabase_realtime FOR ALL TABLES;

-- Create a second schema
CREATE SCHEMA personal;

-- USERS
CREATE TYPE public.user_status AS ENUM ('ONLINE', 'OFFLINE');
CREATE TABLE public.users (
  username text primary key,
  data jsonb DEFAULT null,
  age_range int4range DEFAULT null,
  status user_status DEFAULT 'ONLINE'::public.user_status,
  catchphrase tsvector DEFAULT null
);
ALTER TABLE public.users REPLICA IDENTITY FULL; -- Send "previous data" to supabase
COMMENT ON COLUMN public.users.data IS 'For unstructured data and prototyping.';


-- CREATE A ZERO-TO-ONE RELATIONSHIP (User can have profile, but not all of them do)
CREATE TABLE public.user_profiles (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username text REFERENCES users
);

-- CREATE A TABLE WITH TWO RELATIONS TO SAME DESTINATION WHICH WILL NEED HINTING
CREATE TABLE public.best_friends (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  -- Thoses relations should always be satisfied, never be null
  first_user text REFERENCES users NOT NULL,
  second_user text REFERENCES users NOT NULL,
  -- This relation is nullable, it might be null
  third_wheel text REFERENCES users
);


-- CHANNELS
CREATE TABLE public.channels (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  data jsonb DEFAULT null,
  slug text
);
ALTER TABLE public.users REPLICA IDENTITY FULL; -- Send "previous data" to supabase
COMMENT ON COLUMN public.channels.data IS 'For unstructured data and prototyping.';

create table public.channel_details (
  id bigint primary key references channels(id),
  details text default null
);

-- MESSAGES
CREATE TABLE public.messages (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  data jsonb DEFAULT null,
  message text,
  username text REFERENCES users NOT NULL,
  channel_id bigint REFERENCES channels NOT NULL
);
ALTER TABLE public.messages REPLICA IDENTITY FULL; -- Send "previous data" to supabase
COMMENT ON COLUMN public.messages.data IS 'For unstructured data and prototyping.';

-- USERS AUDIT
CREATE TABLE public.users_audit (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  created_at timestamptz DEFAULT now(),
  previous_value bigint
);

-- SELF REFERENCING TABLE
CREATE TABLE public.collections (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  description text,
  parent_id bigint
);
ALTER TABLE public.messages REPLICA IDENTITY FULL; -- Send "previous data" to supabase
-- SELF REFERENCE via parent_id
ALTER TABLE public.collections
ADD CONSTRAINT collections_parent_id_fkey
FOREIGN KEY (parent_id)
REFERENCES public.collections(id);
COMMENT ON COLUMN public.messages.data IS 'For unstructured data and prototyping.';

-- MANY-TO-MANY RELATIONSHIP USING A JOIN TABLE

-- Create a table for products
CREATE TABLE public.products (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  description text,
  price decimal(10, 2) NOT NULL
);

-- Create a table for categories
CREATE TABLE public.categories (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  description text
);

-- Create a join table for the many-to-many relationship between products and categories
CREATE TABLE public.product_categories (
  product_id bigint REFERENCES public.products(id) ON DELETE CASCADE,
  category_id bigint REFERENCES public.categories(id) ON DELETE CASCADE,
  PRIMARY KEY (product_id, category_id)
);


-- STORED FUNCTION
CREATE FUNCTION public.get_status(name_param text)
RETURNS user_status AS $$
  SELECT status from users WHERE username=name_param;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION public.get_username_and_status(name_param text)
RETURNS TABLE(username text, status user_status) AS $$
  SELECT username, status from users WHERE username=name_param;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION public.offline_user(name_param text)
RETURNS user_status AS $$
  UPDATE users SET status = 'OFFLINE' WHERE username=name_param
  RETURNING status;
$$ LANGUAGE SQL VOLATILE;

CREATE FUNCTION public.set_users_offline(name_param text)
RETURNS SETOF users AS $$
  UPDATE users SET status = 'OFFLINE' WHERE username LIKE name_param RETURNING *;
$$ LANGUAGE SQL;

CREATE FUNCTION public.void_func()
RETURNS void AS $$
$$ LANGUAGE SQL;

create schema extensions;
create extension postgis schema extensions;

create table public.shops (
  id        int primary key
, address   text
, shop_geom extensions.geometry(POINT, 4326)
);

create view public.non_updatable_view as
  select username from public.users limit 1;

create view public.updatable_view as
  select username, 1 as non_updatable_column from public.users;

-- SECOND SCHEMA USERS
CREATE TYPE personal.user_status AS ENUM ('ONLINE', 'OFFLINE');
CREATE TABLE personal.users(
  username text primary key,
  data jsonb DEFAULT null,
  age_range int4range DEFAULT null,
  status user_status DEFAULT 'ONLINE'::public.user_status
);

-- SECOND SCHEMA STORED FUNCTION
CREATE FUNCTION personal.get_status(name_param text)
RETURNS user_status AS $$
  SELECT status from users WHERE username=name_param;
$$ LANGUAGE SQL IMMUTABLE;

create function public.function_with_optional_param(param text default '')
returns text as $$
  select param;
$$ language sql immutable;

create function public.function_with_array_param(param uuid[])
returns void as '' language sql immutable;

create table public.cornercase (
  id        int primary key,
  "column whitespace" text,
  array_column text[]
);

-- Function that returns a single user profile for a user
CREATE OR REPLACE FUNCTION public.get_user_profile(user_row users)
RETURNS user_profiles
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.user_profiles WHERE username = user_row.username LIMIT 1;
$$;

-- Same definition, but will be used with a database.override to pretend this can't ever return null
CREATE OR REPLACE FUNCTION public.get_user_profile_non_nullable(user_row users)
RETURNS SETOF user_profiles
LANGUAGE SQL STABLE
ROWS 1
AS $$
  SELECT * FROM public.user_profiles WHERE username = user_row.username;
$$;


CREATE OR REPLACE FUNCTION public.get_messages(channel_row channels)
RETURNS SETOF messages
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.messages WHERE channel_id = channel_row.id;
$$;

CREATE OR REPLACE FUNCTION public.get_messages(user_row users)
RETURNS SETOF messages
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.messages WHERE username = user_row.username;
$$;

-- Create a view based on users table
CREATE VIEW public.active_users AS
    SELECT * FROM public.users WHERE status = 'ONLINE'::public.user_status;

-- Create a view based on messages table
CREATE VIEW public.recent_messages AS
    SELECT * FROM public.messages ORDER BY id DESC LIMIT 100;

-- Function returning messages using scalar as input (username)
CREATE OR REPLACE FUNCTION public.get_messages_by_username(search_username text)
RETURNS SETOF messages
LANGUAGE SQL STABLE
AS $$
    SELECT * FROM public.messages WHERE username = search_username;
$$;

-- Function returning messages using table row as input
CREATE OR REPLACE FUNCTION public.get_user_messages(user_row users)
RETURNS SETOF messages
LANGUAGE SQL STABLE
AS $$
    SELECT * FROM public.messages WHERE username = user_row.username;
$$;

-- Function returning messages using view row as input
CREATE OR REPLACE FUNCTION public.get_active_user_messages(active_user_row active_users)
RETURNS SETOF messages
LANGUAGE SQL STABLE
AS $$
    SELECT * FROM public.messages WHERE username = active_user_row.username;
$$;

-- Function returning view using scalar as input
CREATE OR REPLACE FUNCTION public.get_recent_messages_by_username(search_username text)
RETURNS SETOF recent_messages
LANGUAGE SQL STABLE
AS $$
    SELECT * FROM public.recent_messages WHERE username = search_username;
$$;

-- Function returning view using table row as input
CREATE OR REPLACE FUNCTION public.get_user_recent_messages(user_row users)
RETURNS SETOF recent_messages
LANGUAGE SQL STABLE
AS $$
    SELECT * FROM public.recent_messages WHERE username = user_row.username;
$$;
CREATE OR REPLACE FUNCTION public.get_user_recent_messages(active_user_row active_users)
RETURNS SETOF recent_messages
LANGUAGE SQL STABLE
AS $$
    SELECT * FROM public.recent_messages WHERE username = active_user_row.username;
$$;
CREATE OR REPLACE FUNCTION public.get_user_first_message(active_user_row active_users)
RETURNS SETOF recent_messages ROWS 1
LANGUAGE SQL STABLE
AS $$
    SELECT * FROM public.recent_messages WHERE username = active_user_row.username ORDER BY id ASC LIMIT 1;
$$;


-- Valid postgresql function override but that produce an unresolvable postgrest function call
create function postgrest_unresolvable_function() returns void language sql as '';
create function postgrest_unresolvable_function(a text) returns int language sql as 'select 1';
create function postgrest_unresolvable_function(a int) returns text language sql as $$ 
    SELECT 'foo' 
$$;
-- Valid postgresql function override with differents returns types depending of different arguments
create function postgrest_resolvable_with_override_function() returns void language sql as '';
create function postgrest_resolvable_with_override_function(a text) returns int language sql as 'select 1';
create function postgrest_resolvable_with_override_function(b int) returns text language sql as $$ 
    SELECT 'foo' 
$$;
-- Function overrides returning setof tables
create function postgrest_resolvable_with_override_function(profile_id bigint) returns setof user_profiles language sql stable as $$
    SELECT * FROM user_profiles WHERE id = profile_id;
$$;
create function postgrest_resolvable_with_override_function(cid bigint, search text default '') returns setof messages language sql stable as $$
    SELECT * FROM messages WHERE channel_id = cid AND message = search;
$$;
-- Function override taking a table as argument and returning a setof
create function postgrest_resolvable_with_override_function(user_row users) returns setof messages language sql stable as $$
    SELECT * FROM messages WHERE messages.username = user_row.username;
$$;

create or replace function public.polymorphic_function_with_different_return(bool) returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_different_return(int) returns int language sql as 'SELECT 2';
create or replace function public.polymorphic_function_with_different_return(text) returns text language sql as $$ SELECT 'foo' $$;

create or replace function public.polymorphic_function_with_no_params_or_unnamed() returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_no_params_or_unnamed(bool) returns int language sql as 'SELECT 2';
create or replace function public.polymorphic_function_with_no_params_or_unnamed(text) returns text language sql as $$ SELECT 'foo' $$;
-- Function with a single unnamed params that isn't a json/jsonb/text should never appears in the type gen as it won't be in postgrest schema
create or replace function public.polymorphic_function_with_unnamed_integer(int) returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_unnamed_json(json) returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_unnamed_jsonb(jsonb) returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_unnamed_text(text) returns int language sql as 'SELECT 1';

-- Functions with unnamed parameters that have default values
create or replace function public.polymorphic_function_with_unnamed_default() returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_unnamed_default(int default 42) returns int language sql as 'SELECT 2';
create or replace function public.polymorphic_function_with_unnamed_default(text default 'default') returns text language sql as $$ SELECT 'foo' $$;

-- Functions with unnamed parameters that have default values and multiple overloads
create or replace function public.polymorphic_function_with_unnamed_default_overload() returns int language sql as 'SELECT 1';
create or replace function public.polymorphic_function_with_unnamed_default_overload(int default 42) returns int language sql as 'SELECT 2';
create or replace function public.polymorphic_function_with_unnamed_default_overload(text default 'default') returns text language sql as $$ SELECT 'foo' $$;
create or replace function public.polymorphic_function_with_unnamed_default_overload(bool default true) returns int language sql as 'SELECT 3';

-- Function creating a computed field
create function public.blurb_message(public.messages) returns character varying as
$$
select substring($1.message, 1, 3);
$$ language sql stable;


create or replace function public.function_returning_row()
returns public.users
language sql
stable
as $$
  select * from public.users limit 1;
$$;


create or replace function public.function_returning_single_row(messages public.messages)
returns public.users
language sql
stable
as $$
  select * from public.users limit 1;
$$;

create or replace function public.function_returning_set_of_rows()
returns setof public.users
language sql
stable
as $$
  select * from public.users;
$$;


-- Function that returns a single element
CREATE OR REPLACE FUNCTION public.function_using_table_returns(user_row users)
RETURNS user_profiles
LANGUAGE SQL STABLE
AS $$
  SELECT * FROM public.user_profiles WHERE username = user_row.username LIMIT 1;
$$;

CREATE OR REPLACE FUNCTION public.function_using_setof_rows_one(user_row users)
RETURNS SETOF user_profiles
LANGUAGE SQL STABLE
ROWS 1
AS $$
  SELECT * FROM public.user_profiles WHERE username = user_row.username LIMIT 1;
$$;

-- Function that return the created_ago computed field
CREATE OR REPLACE FUNCTION "public"."created_ago" ("public"."users_audit") RETURNS numeric LANGUAGE "sql"
SET
  "search_path" TO ''
AS $_$
  SELECT ROUND(EXTRACT(EPOCH FROM (NOW() - $1.created_at)));
$_$;

-- Create a partitioned table for testing computed fields on partitioned tables
CREATE TABLE public.events (
  id bigint generated by default as identity,
  created_at timestamptz default now(),
  event_type text,
  data jsonb,
  primary key (id, created_at)
) partition by range (created_at);

-- Create partitions for the events table
CREATE TABLE public.events_2024 PARTITION OF public.events
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE public.events_2025 PARTITION OF public.events
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Insert some test data
INSERT INTO public.events (created_at, event_type, data)
VALUES
  ('2024-06-15', 'login', '{"user": "alice"}'),
  ('2025-03-20', 'logout', '{"user": "bob"}');

-- Function that returns computed field for partitioned table
CREATE OR REPLACE FUNCTION "public"."days_since_event" ("public"."events") RETURNS numeric LANGUAGE "sql"
SET
  "search_path" TO '' AS $_$
  SELECT ROUND(EXTRACT(EPOCH FROM (NOW() - $1.created_at)) / 86400);
$_$;